package com.lexing;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.logging.Logger;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.lexing.bean.Product;
import com.lexing.bean.ProductFilter;
import com.lexing.bean.ProductType;

public class Main {

	private static SqliteHelper sqliteHelper;

	public static void main(String[] args) {
		BufferedReader strin = new BufferedReader(new InputStreamReader(System.in));
		System.out.print("请输入你的excel文件(回车默认为asset文件夹下的app.xlsx)：");
		String filePath;
		try {
			filePath = strin.readLine();
		} catch (IOException e2) {
			e2.printStackTrace();
			return;
		}
		if (filePath.length() == 0) {
			filePath = Config.ASSET_PATH + "/app.xlsx";
		}
		FileInputStream fis;
		try {
			fis = new FileInputStream(new File(filePath));
		} catch (FileNotFoundException e) {
			System.out.println("Excel 不存在");
			e.printStackTrace();
			return;
		}
		Calendar calendar = Calendar.getInstance();
		String appName = calendar.get(Calendar.YEAR) + "_" + (calendar.get(Calendar.MONTH)+1) + "_"
				+ calendar.get(Calendar.DAY_OF_MONTH) + "_" + "app.db";
		String dbName = Config.DES_PATH +"/"+ appName;
		try {
			copyNewDb(dbName);
		} catch (IOException e1) {
			System.out.println("拷贝数据库出错");
			e1.printStackTrace();
			return;
		}
		try {
			Workbook wb = WorkbookFactory.create(fis);
			Sheet sheet0 = wb.getSheetAt(0);// 产品
			Sheet sheet1 = wb.getSheetAt(1);// 环境
			Sheet sheet2 = wb.getSheetAt(2);// 外部
			Sheet sheet3 = wb.getSheetAt(3);// 内部
			Sheet sheet4 = wb.getSheetAt(4);// 产品类型

			sqliteHelper = SqliteHelper.getInstance(dbName);
			System.out.println("正在写入产品类型数据....");
			sqliteHelper.insertProductType(ExcelHandler.readProductType(sheet4));
			System.out.println("正在写入环境数据....");
			sqliteHelper.insertEnviroment(ExcelHandler.readEnviroment(sheet1));
			System.out.println("正在写入外部条件数据....");
			sqliteHelper.insertOuterCondition(ExcelHandler.readBaseConditions(sheet2));
			System.out.println("正在写入内部条件数据....");
			sqliteHelper.insertInnerCondition(ExcelHandler.readBaseConditions(sheet3));
			System.out.println("正在写入产品数据....");
			List<Product> products = ExcelHandler.readProducts(sheet0);
			sqliteHelper.insertProducts(products);
			for (Product product : products) {
				String filter = product.getFilter();
				if (filter != null && !filter.equals("")) {
					dealFilter(filter, product.getProductId());
				}
			}
			System.out.println("over....");
			sqliteHelper.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 处理筛选
	 * 
	 * @param filter
	 * @throws SQLException
	 * @throws NumberFormatException
	 */
	private static void dealFilter(String filter, long productId) throws NumberFormatException, SQLException {
		String[] strings = filter.split(";");
		ArrayList<ProductFilter> list = new ArrayList<ProductFilter>();
		for (String string : strings) {
			String[] conditions = string.split(",");
			// 0,1,2环境，外部条件，内部条件
			System.out.println("正在更新外部条件外键....");
			sqliteHelper.updateOuterConditions(Long.parseLong(conditions[1]), Long.parseLong(conditions[0]));
			System.out.println("正在更新内部条件外键....");
			sqliteHelper.updateInnerConditions(Long.parseLong(conditions[2]), Long.parseLong(conditions[1]));
			ProductFilter productFilter = new ProductFilter();
			productFilter.setInnerConditionsId(Long.parseLong(conditions[2]));
			productFilter.setProductId(productId);
			list.add(productFilter);
		}
		sqliteHelper.insertFilters(list);
		System.out.println("正在写入筛选关联表数据....");
	}

	private static void copyNewDb(String des) throws IOException {
		File file = new File(des);
		if (file.exists())
			file.delete();
		BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File(Config.BLANK_DB_PATH)));
		BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(new File(des)));
		int len = 0;
		byte[] buffer = new byte[1024 * 4];
		while ((len = bis.read(buffer)) != -1) {
			bos.write(buffer, 0, len);
		}
		bos.close();
		bis.close();
	}

}
